package study.intconsulting.repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import study.intconsulting.vo.PcInfo;


public class ReHsql{
	
	private static String url = "jdbc:hsqldb:hsql://localhost:9001/test"; 
	private static String user = "sa"; 
	private static String password = "";
	
	// get db 접속 이력을 return한다.
	public static List rede(){
		Connection conn	= null; 
		Statement stmt	= null; 
		ResultSet rs	= null;  
		HashMap reqMap	= new HashMap();
		ArrayList reqList	= new ArrayList();
		
		try {        
			Class.forName("org.hsqldb.jdbcDriver");

			conn = DriverManager.getConnection(url, user, password); 
			System.out.println("<p>DB Connection..</p>"); 

			String sQuery = "SELECT IP, PCNAME, WHO, IN_DT FROM SERCH_DT"; 
			stmt = conn.createStatement(); 
			rs = stmt.executeQuery(sQuery);

			while (rs.next()) { 
				reqMap.put("ip",		rs.getString(1));	// 접속ip
				reqMap.put("pcname",	rs.getString(2));	// 접속pc name
				reqMap.put("who",		rs.getString(3));	// 접속자명
				reqMap.put("in_dt",		rs.getString(4));	// 생성시간
				
				reqList.add(reqMap.toString()) ;
			} 

			conn.close();
			
		} catch(Exception ex) { 
			ex.printStackTrace(); 
		} finally { 
			if (rs != null) try {rs.close(); } catch (Exception ex) {} 
			if (stmt != null) try {stmt.close(); } catch (Exception ex) {} 
			if (conn != null) try {conn.close(); } catch (Exception ex) {} 
		} 	
		
		System.out.println(reqList.toString());

		return reqList;
	}
	
	// set db 접속 이력을 등록한다.
	public static void write(PcInfo pcInfo){
		Connection conn = null; 
		Statement stmt = null; 
		ResultSet rs = null;  
		
		try {        
			Class.forName("org.hsqldb.jdbcDriver");

			conn = DriverManager.getConnection(url, user, password); 
			System.out.println("<p>DB Connection..</p>"); 

			String	sQuery = "INSERT INTO SERCH_DT (IP, PCNAME, WHO, IN_DT )";
					sQuery += " VALUES ";
					sQuery += " ( '"+pcInfo.getIp()+"'";
					sQuery += "  ,'"+pcInfo.getName()+"'";
					sQuery += "  ,'"+pcInfo.getWho()+"'";
					sQuery += "  , CURRENT_DATE )	";
			stmt = conn.createStatement(); 
			stmt.executeUpdate(sQuery);
			conn.close();
			
		} catch(Exception ex) { 
			ex.printStackTrace(); 
		} finally { 
			if (rs != null) try {rs.close(); } catch (Exception ex) {} 
			if (stmt != null) try {stmt.close(); } catch (Exception ex) {} 
			if (conn != null) try {conn.close(); } catch (Exception ex) {} 
		} 	
	}
	
	// test
	public static void main(String[] args){
		
		PcInfo pcInfo = new PcInfo();
		
		pcInfo.setIp("10.47.123.11");
		pcInfo.setName("YuJH");
		pcInfo.setWho("int");
		
		rede();
		//write(pcInfo);
	}
}